﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using Aspose.Cells;

namespace Cxx.Common
{
    public class AsposeExcelHelper
    {
        /// <summary>
        /// 获取导出对象实例
        /// </summary>
        /// <returns></returns>
        public static AsposeExcelHelper GetInstance()
        {
            return new AsposeExcelHelper();
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="source">DataTable数据集</param>
        public void ExportToWeb(string fileName, Dictionary<string, DataTable> source)
        {
            Workbook workbook = DataTableToWorkbook(source);

            if (workbook != null)
            {
                MemoryStream memoryStream = workbook.SaveToStream();

                ExportStreamToWeb(fileName, memoryStream);
            }
        }

        public Workbook DataTableToWorkbook(Dictionary<string, DataTable> dataTableList)
        {
            Workbook workbook = new Workbook();
            for (int i = 0; i < dataTableList.Count - 1; i++)
            {
                workbook.Worksheets.Add();
            }

            try
            {
                int i = 0;
                foreach (var item in dataTableList)
                {
                    Style style = GetHeaderStyle(workbook);
                    style.IsTextWrapped = true;
                    Worksheet sheet = workbook.Worksheets[i++];
                    sheet.Name = item.Key;
                    Cells cells = sheet.Cells;

                    // 行号
                    int rowNum = 0;
                    // 列号
                    int columNum = 0;
                    // 设置列标题
                    while (columNum < dataTableList[item.Key].Columns.Count)
                    {
                        DataColumn column = dataTableList[item.Key].Columns[columNum];
                        string stringValue = column.Caption ?? column.ColumnName;
                        cells[rowNum, columNum].PutValue(stringValue);
                        cells[rowNum, columNum].SetStyle(style);
                        columNum++;
                    }
                    rowNum++;
                    // 填充表
                    foreach (DataRow row in dataTableList[item.Key].Rows)
                    {
                        for (columNum = 0; columNum < dataTableList[item.Key].Columns.Count; columNum++)
                        {
                            if (row[columNum] is Int32)
                            {
                                cells[rowNum,columNum].PutValue(Convert.ToInt32(row[columNum]));
                            }
                            else
                            {
                                cells[rowNum, columNum].PutValue(row[columNum].ToString());
                            }
                            var cellStyle = cells[rowNum, columNum].GetStyle();
                            cellStyle.IsTextWrapped = true;
                            cells[rowNum, columNum].SetStyle(cellStyle);
                        }
                        rowNum++;
                    }
                    // 自动列宽
                    sheet.AutoFitColumns();
                    // 自动行高
                    sheet.AutoFitRows();
                    // 固定表头
                    sheet.FreezePanes(1, 0, 1, dataTableList[item.Key].Columns.Count);

                    sheet.AutoFitColumns();
                }


                return workbook;

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取标题栏样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        public Style GetHeaderStyle(Workbook workbook)
        {
            Style style = workbook.Styles[workbook.Styles.Add()];
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.ForegroundColor = Color.FromArgb(0x99, 0xcc, 0);
            style.Pattern = BackgroundType.Solid;
            style.Font.IsBold = true;

            return style;
        }

        /// <summary>
        /// 获取标题栏样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        public Style GetBodyStyle(Workbook workbook)
        {
            Style style = workbook.Styles[workbook.Styles.Add()];
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.IsTextWrapped = true;//单元格内容自动换行 
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

            return style;
        }

        /// <summary>
        /// 输出流到浏览器
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="memoryStream"></param>
        public void ExportStreamToWeb(string fileName, MemoryStream memoryStream)
        {
            //文件名
            string newFileName = fileName;
            if (string.IsNullOrEmpty(newFileName))
            {
                newFileName = Guid.NewGuid().ToString("n");
            }

            //文件名编码
            string encodeFileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);

            //当前上下文
            HttpContext currentContext = HttpContext.Current;

            //设置编码
            currentContext.Response.ContentType = "application/ms-excel";
            currentContext.Response.ContentEncoding = Encoding.UTF8;
            currentContext.Response.Charset = "utf-8";

            //根据不存在浏览器，输出文件
            string userAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"];
            if (userAgent.IndexOf("msie", StringComparison.InvariantCultureIgnoreCase) >= 0)
            {
                currentContext.Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + encodeFileName + "\"");
                //response.AppendHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(disFileName, System.Text.Encoding.UTF8) + "\"");
            }
            else if (userAgent.IndexOf("firefox", StringComparison.InvariantCultureIgnoreCase) >= 0)
            {
                currentContext.Response.AppendHeader("Content-Disposition", "attachment;filename*=utf8''" + fileName);
            }
            else if (userAgent.IndexOf("Trident", StringComparison.InvariantCultureIgnoreCase) >= 0)
            {
                currentContext.Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + encodeFileName + "\"");
            }
            else
            {
                currentContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            }

            currentContext.Response.BinaryWrite(memoryStream.GetBuffer());
        }

    }
}
